/*
 DNN Content Builder - sourceforge.net/projects/dnncb
 Copyright (c) 2004-2008
 by Emerson Lopes (emersonlopes@gmail.com)

 Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
 documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
 the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
 to permit persons to whom the Software is furnished to do so, subject to the following conditions:

 The above copyright notice and this permission notice shall be included in all copies or substantial portions 
 of the Software.

 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
 TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
 THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
 CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
 DEALINGS IN THE SOFTWARE.
*/

using System;
using System.Collections;
using System.Data;
using System.Data.OleDb;
using System.Text;
using DNNContentBuilder.UserControls;

namespace DNNContentBuilder
{
	class MasterSearchController
	{
		#region Members Variables
		private readonly int formDefinitionId;
		private readonly Hashtable searchFields;
		private string drivingTable;
		private string pkIdentifier;
		private string pkName;
		#endregion

		#region Constants
		private const string resource = "~/DesktopModules/DNNContentBuilder/App_LocalResources/ResourceEngine";
		private const string IGNORE_USER_ROLES = "<IGNORE_ROLES>";
		#endregion

		#region Constructors
		/// <summary>
		/// No public constructor, no "new".
		/// </summary>
		public MasterSearchController(int formDefinitionId, Hashtable searchFields)
		{
			this.formDefinitionId = formDefinitionId;
			this.searchFields = searchFields;
		}
		#endregion

		#region Methods
		/// <summary>
		/// Gets an <see cref="ArrayList"/> that contains rows that meet the search
		/// criteria specified in <i>searchFields</i>.
		/// </summary>
		/// <returns></returns>
		public ArrayList GetByUserCriteria()
		{
			//TODO: this method needs improvement
			
			using (IDataReader idr = MetaDataByUserCriteria())
			{
				
				ArrayList infoList = new ArrayList();
				
				MetaDataController mdController = new MetaDataController();
				
				while (idr.Read()) {
					if (idr.GetString(0)!= null) {
						MetaDataInfo mdInfo = mdController.GetMetaDataByPKValue(idr.GetString(0), IGNORE_USER_ROLES);
						
						if (mdInfo != null) {
							infoList.Add(mdController.GetMetaDataByPKValue(idr.GetString(0), IGNORE_USER_ROLES));
						}
					}
				}

				//ArrayList infoList = CBO.FillCollection(idr, typeof(MetaDataInfo));
				
				return infoList;
			}
		}

		private DataSet UserDataByUserCriteria()
		{
			// TODO: retrieve all user data by the search criteria specified.
			return null;
		}

		/// <summary>
		/// Gets a datareader that meets the search criteria specified.
		/// </summary>
		/// <returns></returns>
		private IDataReader MetaDataByUserCriteria()
		{
			// TODO: have to change this. The user table does not necessarily live in
			// the same database or physical server as the portal tables.

			// Retrieve connection string
			string connectionString = Commons.GetConnectionString(this.formDefinitionId);

			ArgumentValidator.AssertNotNull(connectionString, "INVALID_FORM_DEFINITION_CONNSTRING");

			FieldDefinitionController controller = new FieldDefinitionController();
			ArrayList fields = controller.GetByFormDefinition(formDefinitionId);

			string fromClause  = BuildFromClause(fields);
			string whereClause = BuildWhereClause(fields);

			OleDbConnection conn = new OleDbConnection(connectionString);
			OleDbCommand command = conn.CreateCommand();

			conn.Open();
		
			command.CommandText = GetCommandText(fromClause, whereClause);
			command.CommandType = CommandType.Text;
			
			return command.ExecuteReader(CommandBehavior.CloseConnection);
		}

		/// <summary>
		/// Generate a FROM clause for a SQL SELECT command with the tables
		/// necessary for the search criteria specified in the <i>criteria</i>
		/// parameter.
		/// </summary>
#pragma warning disable 1574
		/// <param name="criteria"></param>
#pragma warning restore 1574
		/// <returns></returns>
		private string BuildFromClause(ArrayList fields)
		{
			// TODO: have to change this. The user table does not necessarily live in
			// the same database or physical server as the portal tables.
			StringBuilder result = new StringBuilder();
			
			//result.Append("cbMetaData m");
			
			drivingTable = FormEngine.GetDrivingTable(fields);

			// Join with the driving table, if applicable
			if (drivingTable != null && drivingTable.Length > 0)
			{
				//result.AppendFormat(", {0}", drivingTable);
				result.AppendFormat("{0}", drivingTable);
			}

			foreach (FieldDefinitionInfo fdInfo in fields)
			{
				string fieldName = fdInfo.SourceField;

				// Only consider fields that have some search criteria
				if (this.searchFields[fieldName] != null)
				{
					string searchCriteria = ((ISearchControl)this.searchFields[fieldName]).SearchCriteria;

					if (searchCriteria == null)
					{
						continue;
					}
					
					if (result.ToString().IndexOf(fdInfo.TableName) < 0)
					{
						result.AppendFormat(", {0}", fdInfo.TableName);
					}
				}
			}

			return result.ToString();
		}

		/// <summary>
		/// Generate a WHERE clause for a SQL SELECT command with the search 
		/// criteria specified in the <i>criteria</i> parameter.
		/// </summary>
		/// <returns></returns>
		private string BuildWhereClause(ArrayList fields)
		{
			// TODO: have to change this. The user table does not necessarily live in
			// the same database or physical server as the portal tables.
			const string expr = " and ";
			StringBuilder result = new StringBuilder();

			bool hasPK = false;

			foreach (string fieldName in this.searchFields.Keys) 
			{
				if (fieldName.IndexOf(".PK") > -1) 
				{
					hasPK = true;
					break;
				}
			}

			if (!hasPK)
			{
				throw new ApplicationException(Commons.GetLocalizedString("PK_NOT_FOUND", resource));
			}

			drivingTable = FormEngine.GetDrivingTable(fields);
			pkIdentifier = String.Format("{0}.PK", drivingTable);

			pkName = searchFields[pkIdentifier].ToString();

			// All remaining criteria built here.
			foreach (FieldDefinitionInfo fdInfo in fields)
			{
				string fieldName = fdInfo.SourceField;
				
				// The primary key field has been processed already, so simply ignore it.
				if (fieldName != pkIdentifier &&  this.searchFields[fieldName] != null)
				{
					string searchCriteria = ((ISearchControl)this.searchFields[fieldName]).SearchCriteria;

					if (searchCriteria == null)
					{
						continue;
					}
					
					if (result.ToString() == "") {
						result.AppendFormat("{0}{1} ", fieldName, searchCriteria);
					} else {
						result.AppendFormat("{0}{1}{2} ", expr, fieldName, searchCriteria);
					}

					// Test for a linker search field, i.e., one that links to another table
					if (fdInfo.TableName != drivingTable)
					{
						string fkColumnName = GetLinkerField(fields, fdInfo.TableName);
						string fkExpression = String.Format("{0} = {1}", fkColumnName, searchFields[pkIdentifier]);

						if (result.ToString().IndexOf(fkExpression) == -1)
						{
							result.AppendFormat("{0}{1}", expr, fkExpression);
						}
					}
				}
			}

			return result.ToString();
		}

		private static string GetLinkerField(ArrayList fields, string tableName)
		{
			string result = null;

			foreach (FieldDefinitionInfo fdInfo in fields)
			{
				if (fdInfo.TableName == tableName && fdInfo.IsLinker)
				{
					return fdInfo.SourceField;
				}
			}

			return result;
		}

		/// <summary>
		/// Generate a SQL SELECT command with the supplied FROM and WHERE clauses.
		/// </summary>
		/// <param name="fromClause"></param>
		/// <param name="whereClause"></param>
		/// <returns></returns>
		private string GetCommandText(string fromClause, string whereClause)
		{
			string cmdText = @"
				select	{0}
				from  {1}";
			
			if (whereClause != "") {
				cmdText += " where {2}";
				return 	String.Format(cmdText, pkName, fromClause, whereClause);
			}
			
			return String.Format(cmdText, pkName, fromClause);
		}
		#endregion
	}
}
